USE [eLogitics]
GO

/****** Object:  StoredProcedure [dbo].[QueryIssuingOrderDelivery]    Script Date: 04/17/2012 10:27:19 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[QueryIssuingOrderDelivery]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[QueryIssuingOrderDelivery]
GO

USE [eLogitics]
GO

/****** Object:  StoredProcedure [dbo].[QueryIssuingOrderDelivery]    Script Date: 04/17/2012 10:27:19 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

-- =============================================
-- Author:		<Author,,Name>
-- Create date: <Create Date,,>
-- Description:	<Description,,>
-- =============================================
Create PROCEDURE [dbo].[QueryIssuingOrderDelivery]
	@OrderCode nvarchar(50),
	@CustomerId uniqueidentifier,
	@TransportationPlanCode nvarchar(50),
	@OrderStatus int,
	@RouteTypeId uniqueidentifier,
	@ReceivingOrderDateStart datetime = null,
	@ReceivingOrderDateEnd datetime =null, 
	@GoodsShippedTypeId uniqueidentifier,
	@DeliveryCity nvarchar(6),
	@AreaId uniqueidentifier
	
	 
	
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

    -- Insert statements for procedure here
	SELECT
	OrderCode,
IssuingOrderId
	  DeliveryOrderCode,
	  DeliveryOrderType =dbo.GeDataDictionary(DeliveryOrderType),
	  DeliveryAddress

	  from  IssuingOrderDelivery
	left join IssuingOrder on IssuingOrder.Id = IssuingOrderDelivery.IssuingOrderId
        where (OrderCode like '%' + @OrderCode+'%' or @OrderCode is null) 
        and (IssuingOrder.CustomerId =@CustomerId or @CustomerId is null)
        and (TransportationPlanCode like '%'+@TransportationPlanCode+'%' or @TransportationPlanCode is null)
        and (OrderStatus =@OrderStatus or @OrderStatus is null)
        and (RouteTypeId =@RouteTypeId or @RouteTypeId is null)
        and (GoodsShippedTypeId =@GoodsShippedTypeId or @GoodsShippedTypeId is null)
         and ((DeliveryCity =@DeliveryCity  or DeliveryCity1 =@DeliveryCity or DeliveryCity2 =@DeliveryCity  or DeliveryCity3 =@DeliveryCity  or DeliveryCity4 =@DeliveryCity )or @DeliveryCity is null)

          and (IssuingOrder.AreaId =@AreaId or @AreaId is null)  
         and  (ReceivingOrderDate >=@ReceivingOrderDateStart or @ReceivingOrderDateStart is null)
         and  (ReceivingOrderDate <=@ReceivingOrderDateEnd or @ReceivingOrderDateEnd is null)
         
END

GO


